﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using _4T.Data;
using System.Transactions;

namespace _4T.DAL
{
    public class InfoSupplierInfoDAL
    {
        public InfoSupplierInfoDAL() { }

        public int InsertInfoSupplierInfo(string supplierName, string phoneNumber, string personalCode, string email, string note)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    SqlConnection conn = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        conn.Open();
                        cmd.CommandText = "InfoSupplierInfoInsert";
                        cmd.CommandType = CommandType.StoredProcedure;

                        if (personalCode == "guestsoncamedia")
                        {
                            cmd.Parameters.Add("@SupplierName", SqlDbType.NText).Value = supplierName;
                            cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 20).Value = phoneNumber;
                            cmd.Parameters.Add("@Email", SqlDbType.NText).Value = email;
                        }
                        else
                        {
                            PersonalInfoDAL personDAL = new PersonalInfoDAL();
                            PersonalInfo person = personDAL.GetPersonalInfo(personalCode);
                            if (person != null)
                            {
                                cmd.Parameters.Add("@SupplierName", SqlDbType.NText).Value = person.FullName;
                                cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 20).Value = person.PhoneNumber;
                                cmd.Parameters.Add("@Email", SqlDbType.NText).Value = person.Email;
                            }
                            else
                            {
                                return 0;
                            }
                        }

                        cmd.Parameters.Add("@PersonalCode", SqlDbType.NText).Value = personalCode;
                        cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                        cmd.Connection = conn;

                        result = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                    scope.Complete();
                }
            }

            return result;
        }

        public int InsertInfoSupplierInfoFromCMS(string supplierName, string phoneNumber, string personalCode, string email, string note)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    SqlConnection conn = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        conn.Open();
                        cmd.CommandText = "InfoSupplierInfoInsert";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@SupplierName", SqlDbType.NText).Value = supplierName;
                        cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 20).Value = phoneNumber;
                        cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = personalCode;
                        cmd.Parameters.Add("@Email", SqlDbType.NText).Value = email;
                        cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                        cmd.Connection = conn;

                        result = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                    scope.Complete();
                }
            }

            return result;
        }

        public InfoSupplierInfo GetInfoSupplierInfo(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();

                InfoSupplierInfo supplier = null;
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "InfoSupplierInfoGet";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        supplier = GetInfoSupplierFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return supplier;
            }

            return null;
        }

        public InfoSupplierInfo GetInfoSupplierInfo2(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();

                InfoSupplierInfo supplier = null;
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "InfoSupplierInfoGet2";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        supplier = GetInfoSupplierFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return supplier;
            }

            return null;
        }

        private InfoSupplierInfo GetInfoSupplierFromReader(SqlDataReader reader)
        {
            string supplierName = reader.GetString(0);

            string phone = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                phone = reader.GetString(1);

            string personalCode = string.Empty;
            if (reader.GetValue(2) != DBNull.Value)
                personalCode = reader.GetString(2);

            string email = string.Empty;
            if (reader.GetValue(3) != DBNull.Value)
                email = reader.GetString(3);

            string note = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                note = reader.GetString(4);

            return new InfoSupplierInfo(supplierName, phone, personalCode, email, note);
        }
    }
}